OpenEdge Data Management:<br />DataServer for ORACLE Indexes

You create and maintain all indexes from within ORACLE. When you create a schema image from a target ORACLE database, OpenEdge automatically copies the ORACLE index definitions.

Indexes allow you to use the OF keyword in the Progress 4GLwith FOR EACH and FIND statements. Using the OF keyword improves the readability of your code. The OF keyword is a shorter version of a WHERE clause. You can use OF only when you have a field of the same name in two tables and this field is a unique index in at least one of the tables. You can then write the following statement:

FOR EACH customer OF order:

Index definitions support the Progress 4GL USE-INDEX modifier. The Progress 4GL translates USE-INDEX to ORDER BY for DataServer operations. For example, if you define city-dept as an index on the city and department fields, the following Progress 4GL statements are equivalent when accessing an ORACLE database:

ORACLE chooses which index, if any, to use when the OpenEdge application accesses information in the ORACLE database. However, the DataServer passes an index hint to ORACLE that specifies the index to use for a query and in which order to read the index. The hints take the form of comments in the SQL code generated by the DataServer.

FOR EACH employee USE-INDEX city-dept:

FOR EACH employee BY city BY department:

If you use the Progress 4GL USE-INDEX modifier in your code, the DataServer generates a hint telling ORACLE which to use. The DataServer considers the direction of your query and whether you declared the first component of your index to be ascending or descending. The DataServer then issues an SQL statement to ORACLE that it should read the index either forward or backward to ensure that it retrieves the records in the order you specified.

By including the USE-INDEX modifier in your Progress 4GL code, you can enhance ORACLE performance, especially in cases where your application returns records in a descending order.

If you do not use the Progress 4GL USE-INDEX modifier, the DataServer might generate an index hint based upon the WHERE or BY option. If the WHERE clause has one of the following elements, the DataServer generates an index hint based on the BY option:

The not equal operator (< >).

A function.

An expression.

For example, the DataServer passes an index hint to ORACLE to use cust-num for the following query:


FOR EACH customer WHERE address = "55 Cambridge" BY cust-num:

If you issue a query that includes BY options, the DataServer considers whether the fields for the BY option participate in a compound index and generates an index hint to ORACLE to use that index if the WHERE clause does not imply a different index.

You can prevent the DataServer from passing hints to ORACLE by using the NO-INDEX-HINT option for the QUERY-TUNING phrase or by using the -noindexhint startup parameter. See the "Query tuning" section and the "ORACLE hints" section for more information.

Leading and trailing spaces

The Progress 4GL and ORACLE handle leading or trailing blanks in an indexed column differently. For example, when you attempt to create a record in ORACLE and include leading or trailing blanks in a column that participates in a unique index, ORACLE returns a message that there is a duplicate unique key. To address this, the DataServer, by default, trims leading or trailing blanks when you use them in a WHERE clause. If you want to specify leading or trailing blanks, specify the -znotrim startup parameter when you start the OpenEdge client session.